from models import DayPaperModel, EmployeeModel, SubmissionModel, RoleModel, Leave_vacationModel
from datetime import datetime
from datetime import timedelta
from exts import db
from sqlalchemy import func, desc, and_
from services.mail_service import fetch_daily_reports, send_remind_email
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment
import os
from chinese_calendar import is_workday
from utils import Info


def search_date():
    # 获取当前日期
    today = datetime.now().date()

    # 检查表中最新日期
    max_date_result = db.session.query(func.max(DayPaperModel.date)).scalar()  # 查询聚合函数的单个值

    if max_date_result is None:
        set_date = today - timedelta(10)
        return set_date, today, set_date
    else:
        max_date = max_date_result

    min_date_result = db.session.query(func.min(DayPaperModel.date)).scalar()
    if min_date_result is None:
        min_date = datetime.strptime("2024-10-12", "%Y-%m-%d").date()
    else:
        min_date = min_date_result

    return max_date, today, min_date  # 返回<class 'datetime.date'>


def init_update_date(since_date, end_date):
    employees = EmployeeModel.query.all()
    print("初始化日报数据:")
    # 添加数据
    while since_date <= end_date:

        if DayPaperModel.query.filter_by(date=since_date).first():
            # print(f"{since_date}已存在")
            since_date += timedelta(days=1)  # 自增天数
            continue  # 如果当天有数据，跳过循环

        for employee in employees:
            try:
                if is_workday(since_date):
                    submitted = -1
                else:
                    submitted = 0

                leaves = Leave_vacationModel.query.filter_by(em_id=employee.id).all()
                if leaves:
                    for leave in leaves:
                        if leave.start_date <= since_date <= leave.end_date:
                            submitted = leave.status # 设置为2或3
                            break

                daypaper = DayPaperModel(date=since_date, em_id=employee.id, submitted=submitted, weekday=since_date.weekday()+1)
                db.session.add(daypaper)
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                print(f"添加错误，error:{e}")
                return False
        print(f"添加 {since_date} 的初始日期记录")
        since_date += timedelta(days=1)  # 自增天数
    return True


def update_daypaper(since_date=None, end_date=None):
    if not since_date:
        since_date, today, _ = search_date()
        end_date = today - timedelta(days=1)  # yesterday
        auto_flag = True
    else:  # todo: 自定义更新，未制作
        since_date = datetime.strptime(since_date, "%Y-%m-%d").date()
        end_date = datetime.strptime(end_date, "%Y-%m-%d").date()
        auto_flag = False

    status = init_update_date(since_date, end_date)
    if not status:
        print('初始化失败')
        return False, Info("初始化日报出错")

    # 调整日期格式为字符串型
    if auto_flag:  # 如果是自动更新，检索时添加一天
        since_date = since_date + timedelta(days=1)
    since_date = since_date.strftime("%d-%b-%Y")
    end_date = end_date.strftime("%d-%b-%Y")

    # 若想手动设置查询范围
    # since_date = "12-Nov-2024"
    # end_date = "15-Nov-2024"

    status, info = fetch_daily_reports(since_date, end_date)
    if not status:
        return False, info
    report = info.data

    for entry in report:
        name = entry['name']
        date_str = entry["date"]  # 日期为字符串型
        formatted_date = date_str.replace('.', '-')

        employee = EmployeeModel.query.filter_by(name=name).first()  # 获取该员工
        if employee:
            daypaper = DayPaperModel.query.filter_by(
                date=formatted_date,
                em_id=employee.id
            ).first()
            if daypaper:
                # daypaper = next((dp for dp in employee.daypapers if dp.date == formatted_date), None)
                try:
                    # 修改提交值
                    daypaper.submitted = 1
                    db.session.commit()
                    print(f"{daypaper.date} {name} 标记为已提交")
                except Exception as e:
                    db.session.rollback()
                    print(f"修改发生错误,error: {e}")
                    return False, Info("修改提交时发生错误")
        else:
            print(f"检测到 {name} 不在员工表中")
    print("更新完毕!")
    return True, Info("日报更新成功")


def update_vacation(em_id, status, start_date, end_date):  # 根据请假修改日报表
    max_date, today, _ = search_date()  # 获取当日
    print(f"max_date_type:{type(max_date)}, end_date_type:{type(end_date)}")

    end_date = min(end_date, max_date)  # 如果 end date 超过日报表中的最大日期，则修正为最大日期

    # 查询符合条件的日报数据
    daypapers = DayPaperModel.query.filter(
        and_(
            DayPaperModel.em_id == em_id,
            DayPaperModel.date.between(start_date, end_date)
        )
    ).all()
    if daypapers is None:
        print("查询为空")
    for daypaepr in daypapers:
        if daypaepr.submitted != 1:  # 若已提交，不改变已提交状态
            daypaepr.submitted = status

    db.session.commit()
    print("请假成功")
    return True, Info("修改成功")

def fuzzy_search_name(names):
    # 通过if语句传来，肯定存在names
    names_set = set()
    for name in names:
        employees = EmployeeModel.query.filter(EmployeeModel.name.like(f"%{name}%")).all()
        names_set.update(employee.name for employee in employees)
    # print(f"names_set: {names_set}")
    return tuple(names_set)


def fetch_daypaper_data(since_date=None, end_date=None, employee_names=None, role=None, em_id=None):
    if em_id:
        employee = EmployeeModel.query.get(em_id)
        if employee:
            employee_names = (employee.name,)
        else:
            return False, Info("没有找到该员工")
    if role:
        print(role)
        if isinstance(role, int) or (isinstance(role, str) and role.isdigit()):  # 传入数字1或2时
            result = RoleModel.query.get(role)
        elif isinstance(role, str):  # 传入汉字学生时
            result = RoleModel.query.filter_by(description=role).first()
        else:
            print("role的类型错误，查询失败")
            return False, Info("role的类型错误，查询失败")

        if result is None:
            return False, Info("数据库未定义该角色")

        names_tuple = tuple(employee.name for employee in result.employees)
        print(f"角色: {result.description}{names_tuple}")
        # if employee_names:
        #     employee_names = tuple(set(names_tuple) & set(employee_names))
        employee_names = tuple(set(names_tuple) & set(employee_names)) if employee_names else names_tuple
        if not employee_names:
            print("检索员工与角色相冲突，无数据")
            return False, Info("未找到对应员工，可能检索员工与角色相冲突")

    if not employee_names:
        print("检索所有成员")
    else:
        employee_names = fuzzy_search_name(employee_names)
        print(f"检索成员: {employee_names}")
        if not employee_names:
            return False, Info("未找到对应员工")

    # 判断日期
    if not end_date:
        max_date, _, min_date = search_date()
        end_date = max_date  # 数据库中最新的日期
        end_date = end_date.strftime('%Y-%m-%d')
        if not since_date:
            # since_date = end_date - timedelta(days=10)  # 检索特定个数日期
            since_date = min_date
            since_date = since_date.strftime('%Y-%m-%d')

    # 获取员工实例元组
    employees = (  # 返回一个元组的实例
        EmployeeModel.query
        .filter(EmployeeModel.name.in_(employee_names)) if employee_names else EmployeeModel.query.all()
    )
    employee_ids = [employee.id for employee in employees]  # 从实例中获取id

    # 获取在指定日期范围内的考勤记录
    daypapers = (
        DayPaperModel.query
        .filter(DayPaperModel.em_id.in_(employee_ids), DayPaperModel.date.between(since_date, end_date))
        .all()
    )

    # 构建 JSON 数据结构
    data = {
        "summary": {
            "employee_count": len(employee_ids),  # 员工数量
            "date_count": len(set(dp.date for dp in daypapers)),  # 不重复的日期数量
            "since_date": since_date,
            "end_date": end_date
        },
        "records": []
    }

    # 使用字典来汇总每个员工的提交状态
    attendance_dict = {}

    # 遍历考勤记录
    for dp in daypapers:
        id = dp.employee.id
        name = dp.employee.name  # 通过关系属性访问员工姓名
        role = dp.employee.role
        date_str = dp.date.strftime('%Y-%m-%d')  # 将 date 转换为字符串
        submitted = dp.submitted
        weekday = dp.weekday

        if name not in attendance_dict:
            attendance_dict[name] = {
                "role": role,
                "daypapers": {},
                "id": id
            }

        attendance_dict[name]["daypapers"][date_str] = submitted
        # 将日期下的值改为字典，包含 submitted 和 weekday
        # attendance_dict[name][date_str] = {
        #     "submitted": submitted,
        #     "weekday": weekday
        # }

    # print(attendance_dict)

    # 将汇总的数据转换为所需的格式
    for name, info in attendance_dict.items():
        data["records"].append({
            "name": name,
            "role": info["role"],
            "daypapers": info["daypapers"],
            "id": info["id"]
        })

    # return json.dumps(data, ensure_ascii=False)
    return True, Info("查询成功", data)


def output_daypaper_excel(since_date=None, end_date=None, names=None, role=None):
    # 从 JSON 数据中提取 records
    status, info = fetch_daypaper_data(since_date, end_date, names, role)
    if not status:
        return status, Info("导出失败")
    # data = json.loads(info.data)
    data = info.data
    records = data['records']

    # 创建一个列表来存储转换后的数据
    rows = []

    # 遍历每个员工的考勤记录
    for record in records:
        name = record['name']
        daypapers = record['daypapers']

        # 遍历每个日期的考勤记录
        for date, submission in daypapers.items():
            rows.append((name, date, submission, pd.to_datetime(date).isoweekday()))  # 以元组形式存储
    print(rows)
    # 将列表转换为 DataFrame
    df = pd.DataFrame(rows, columns=['name', 'date', 'submitted', 'weekday'])

    # 替换 submitted 列中的数值为可读状态
    status_color_mapping = {
        -1: ("X", "F5757D"),  # 红色
        0: ("/", "A9ACB2"),  # 灰色
        1: ("√", "99FAA3"),  # 绿色
        2: ("请假", "3674B5"),  # 蓝色
        3: ("休假", "409EFF"),  # 蓝色
    }

    df['submitted'] = df['submitted'].replace({key: value[0] for key, value in status_color_mapping.items()})

    df['weekday'] = df['weekday'].replace({
        1: '周一',
        2: '周二',
        3: '周三',
        4: '周四',
        5: '周五',
        6: '周六',
        7: '周日',
    })

    # 使用pivot_table 方法转换DataFrame
    pivot_df = df.pivot_table(
        index='name',  # 行索引设为姓名
        columns='date',  # 列为报告日期
        values='submitted',  # 单元格为提交情况
        fill_value="无数据",  # 若未提交填充为0
        aggfunc='first'  # 使用 'first' 聚合函数处理字符串
    )

    # 重置索引
    pivot_df.reset_index(inplace=True)

    # 增加星期的行
    week_dict = df.set_index('date')['weekday'].to_dict()
    week_row = [week_dict.get(date, '') for date in pivot_df.columns[1:]]
    pivot_df.loc[-1] = [''] + week_row
    pivot_df.index = pivot_df.index + 1
    pivot_df.sort_index(inplace=True)  # 排序索引

    try:
        # 导出为Excel文件
        output_directory = os.path.join(os.path.dirname(__file__), '..', 'output')
        output_file_name = 'output_daypaper.xlsx'
        output_file_path = os.path.join(output_directory, output_file_name)
        pivot_df.to_excel(output_file_path, index=False)

        if not beautify_excel(output_file_path, pivot_df, status_color_mapping):
            print("美化发生错误")

        return True, Info("导出成功", output_file_path)
    except Exception as e:
        print(f"发生错误: {e}")
        return False, Info("导出失败,可能已打开excel")


def beautify_excel(file_path, pivot_df, status_color_mapping):
    # 美化 Excel 文件
    wb = load_workbook(file_path)
    ws = wb.active  # 打开 Excel 文件

    # 合并第一行第一个单元格和第二行第一个单元格
    ws.merge_cells('A1:A2')  # 合并 A1 和 A2

    # 可选：设置合并后单元格的值
    ws['A1'] = '姓名/日期'  # 设置合并后单元格的值

    # 调整列宽
    column_widths = [15] + [13] * (len(pivot_df.columns) - 1)  # 创建固定列宽列表
    # 设置列宽
    for i, column in enumerate(ws.columns):
        column_letter = column[0].column_letter  # 获取列字母
        ws.column_dimensions[column_letter].width = column_widths[i]

    # 设置行高
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row):
        row_height = 32  # 设置行高
        ws.row_dimensions[row[0].row].height = row_height

        # 设置边框样式
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))

    # 居中对齐
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=len(pivot_df.columns)):
        for cell in row:
            cell.alignment = Alignment(horizontal='center', vertical='center')  # 设置居中对齐

            # 设置边框
            cell.border = thin_border  # 设置边框

    # 设置背景颜色
    for row in ws.iter_rows(min_row=2, min_col=2, max_row=ws.max_row, max_col=len(pivot_df.columns)):
        for cell in row:
            for key, value in status_color_mapping.items():
                if cell.value == value[0]:
                    cell.fill = PatternFill(start_color=value[1], end_color=value[1], fill_type="solid")  # 对应颜色
                    # 如果想设置成无背景色，cell.fill = PatternFill(fill_type=None)
            cell.border = thin_border  # 设置边框

    # 保存工作簿
    wb.save(file_path)

    return True, Info("美化成功")


def calculate_submission_rate(date=None):  # 计算提交率以及返回当日未提交名单
    if date is None:
        date, _, _ = search_date()  # 取数据库中当前最新日期（昨天）

    daypapers = DayPaperModel.query.filter_by(date=date).all()
    if not daypapers:
        return False, Info(f"数据库中没有{date}的日报信息")
    submitted_list = [daypaper.submitted for daypaper in daypapers]

    if 0 in submitted_list:
        return True, Info(f"{date} 为休息日，不计算日报提交率")
    else:
        absence_dict = {}  # 改为字典形式，存放不同角色的日报缺少情况
        roles = RoleModel.query.all()
        for role in roles:
            absence_dict[role.description] = {
                'ids': [],  # 存放员工ID
                'names': [],  # 存放员工姓名
            }

        absence_count = submitted_list.count(-1)  # 计算未提交个数
        expected_count = submitted_list.count(1) + absence_count  # 应提交数
        for daypaper in daypapers:
            if daypaper.submitted == -1:
                absence_dict[daypaper.employee.identity.description]['ids'].append(daypaper.employee.id)
                absence_dict[daypaper.employee.identity.description]['names'].append(daypaper.employee.name)

        # absence_list = [daypaper.employee.name for daypaper in daypapers if daypaper.submitted == -1]

        submission_rate_percentage = (1 - absence_count / expected_count) * 100  # 提交率

        print(f"{date}提交率为: {submission_rate_percentage:.2f}%, "
              f"未提交数目：{absence_count}/{expected_count}, "
              f"未提交名单：{absence_dict}")
        filter_dict = {
            "日期": date.strftime("%Y-%m-%d"),
            "提交率": f"{submission_rate_percentage:.2f}%",
            "未提交数目": f"{absence_count}/{expected_count}",
            # "未提交名单": absence_list
            "未提交名单": absence_dict
        }

        return True, Info("计算成功", filter_dict)


def bar_graph(date=None):  # 首页柱状图数据
    if date is None:
        date, _, _ = search_date()

    end_date = date
    start_date = end_date - timedelta(days=6)  # 获取7天的日报数据
    print(start_date, end_date)

    data = []
    while start_date <= date:
        daypapers = DayPaperModel.query.filter_by(date=start_date).all()
        if not daypapers:
            start_date += timedelta(days=1)
            continue
        # 获取 submitted 列表
        submitted_list = [daypaper.submitted for daypaper in daypapers]

        # 计算提交数和应提交数
        submitted_count = submitted_list.count(1)  # 提交数：统计1的数量
        if 0 in submitted_list:  # 若为休息日，应提交数为0
            expected_count = 0
        else:
            expected_count = submitted_list.count(-1) + submitted_count  # 应提交数

        # 获取未提交名单：
        absence_dict = {}
        roles = RoleModel.query.all()
        for role in roles:
            absence_dict[role.description] = []

        for daypaper in daypapers:
            if daypaper.submitted == -1:
                absence_dict[daypaper.employee.identity.description].append(daypaper.employee.name)

        date_str = start_date.strftime("%m-%d")
        filter_data = {
            "日期": date_str,
            "提交数": submitted_count,
            "应提交数": expected_count,
            "未提交名单": absence_dict
        }
        data.append(filter_data)

        start_date += timedelta(days=1)

    print("调用bar")
    return True, Info("成功", data)


def uncommitted_report():  # 发送未提交提醒
    # 自动更新执行的
    max_day, _, _ = search_date()
    daypapers = DayPaperModel.query.filter_by(date=max_day, submitted=-1).all()

    uncommitted_list = [daypaper.employee.email for daypaper in daypapers]  # 获取所有未提交人员的邮箱

    # uncommitted_list = ['1764650170@qq.com']  # 测试邮件

    if uncommitted_list is None:
        return
    else:
        if len(uncommitted_list) > 20:  # 如果未提交人数超过20，可能更新日报出错或其他情况，考虑不发送
            return
        # 调用发送邮件函数
        send_remind_email(max_day, uncommitted_list)
